﻿#region NameSpace
using System;
using System.Collections.Generic;
using System.Text;
using System.Data;
using System.Data.Sql;
using System.Data.SqlClient;
using System.Data.SqlTypes;
using Microsoft.SqlServer.Server;
using System.Configuration;
#endregion

namespace ebird.QueryGenerics
{

    public class QueryGenerics
    {
        #region Fields
        string connectionString = string.Empty;
        SqlCommand command;
        SqlConnection connection;
        DataSet dataSet;
        SqlDataAdapter sqlDataAdapter;
        #endregion

        #region Constructor
        /// <summary>
        /// Connection to database
        /// </summary>
        public QueryGenerics(string connectionString)
        {
            // connectionString = "Data Source=198.64.249.24,1092;Initial Catalog=eInternShip;User Id=bmjobmjo;Password=goodhopealways";// ConfigurationManager.ConnectionStrings["connectUserManager"].ToString();
            connection = new SqlConnection(connectionString);
        }

        #endregion

        #region Methods
        /// <summary>
        /// returns number of rows affected (Use Insert/Update/Delete statement)
        /// </summary>
        /// <returns></returns>
        public int ExecuteNonQuery(string query)
        {
                int count = 0;
                command = new SqlCommand();
                command.Connection = connection;
                command.CommandType = CommandType.Text;
                command.CommandText = query;
                connection.Open();
                count = command.ExecuteNonQuery();
                connection.Close();
                return count;
        }

        /// <summary>
        /// returns DataSet(Use Select Statement)
        /// </summary>
        /// <param name="query"></param>
        /// <returns></returns>
        public DataSet ExecuteQuery(string query)
        {
                dataSet = new DataSet();
                command = new SqlCommand();
                command.Connection = connection;
                command.CommandText = query;
                connection.Open();
                sqlDataAdapter = new SqlDataAdapter(command);
                sqlDataAdapter.Fill(dataSet);
                connection.Close();
                return dataSet;
        }
        /// <summary>
        /// returs single value(1st Column of 1st row)
        /// </summary>
        /// <param name="query"></param>
        /// <returns></returns>
        public object ExecuteScalar(string query)
        {
            Object count;
            command = new SqlCommand();
            command.Connection = connection;
            command.CommandType = CommandType.Text;
            command.CommandText = query;
            connection.Open();
            count = command.ExecuteScalar();
            connection.Close();
            return count;
        }

        //public void PopulateGridView(GridView grvName, string query)
        //{
        //    DataSet gridDS = new DataSet();
        //    gridDS = ExecuteQuery(query);
        //    grvName.DataSource = gridDS;
        //    grvName.DataBind();
        //}

        public SqlDataAdapter SQLDataAdapter()
        {
            sqlDataAdapter = new SqlDataAdapter("", connection);
            return sqlDataAdapter;
        }
        #endregion
    }
}